package cn.kpi.common.Excel.importfile.impl;


import cn.kpi.common.Excel.importfile.FileImportor;
import cn.kpi.common.Excel.importfile.domain.MapResult;
import cn.kpi.common.Excel.importfile.domain.common.Configuration;
import cn.kpi.common.Excel.importfile.domain.common.ImportCell;
import cn.kpi.common.Excel.importfile.domain.common.ImportResult;
import cn.kpi.common.Excel.importfile.domain.userInfoMap;
import cn.kpi.common.Excel.importfile.domain.userPayMap;
import cn.kpi.common.Excel.importfile.exception.FileImportException;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Pattern;

/**
 * Create by liuyong on 2017/11/3
 */

public class ExcelImportor extends FileImportor {
    private Configuration configuration;


    @Override
    public ImportResult getImportResult(File file, String fileName,String tableName) throws FileImportException {
        if (configuration == null) {
            throw new FileImportException("configuration is null");
        }
        StringBuilder stringbuilder = new StringBuilder();
        Workbook workbook = null;
        String extension = fileName.lastIndexOf(".") == -1 ? "" : fileName
                .substring(fileName.lastIndexOf(".") + 1);
        if ("xls".equals(extension)) {
            try {
                workbook = new HSSFWorkbook(new FileInputStream(file));
            } catch (IOException e) {
                throw new FileImportException(e, e.getMessage());
            }
        }
        else if ("xlsx".equals(extension)) {
            try {
                workbook = new XSSFWorkbook(new FileInputStream(file));
            } catch (IOException e) {
                throw new FileImportException(e, e.getMessage());
            }
        }
        else {
            throw new FileImportException("支持.xls版本的excle导入");
        }
        if(checktitle(workbook)) throw new  FileImportException("第4行必须为列名，请按照模版的格式调整！");

        List<Map<String,Object>> result = readExcel(workbook,tableName, configuration, stringbuilder);
        MapResult mapResult = new MapResult();
        mapResult.setResult(result);
        mapResult.setResMsg(stringbuilder.toString());
        return mapResult;
    }

    private Boolean checktitle(Workbook workbook){
        Boolean noTitle = true;
        Sheet sheet = workbook.getSheetAt(0);
        Row titleRow = sheet.getRow(3);
        for(int i =0;i<5;i++){
            Cell cell = titleRow.getCell(i);
            if (cell.getCellType() == Cell.CELL_TYPE_STRING && cell.getStringCellValue().equals("姓名")) {
                noTitle = false;
            }
        }

        return noTitle;
    }

    private List<Map<String,Object>> readExcel(Workbook workbook,String tableName, Configuration configuration, StringBuilder sb) throws FileImportException {
        //选择第一个sheet
        Sheet sheet = workbook.getSheetAt(0);
        int startRow = configuration.getStartRowNo();

        //获取标题行
        int  titleNum = 2;
        if(tableName.equals("UserInfo")) titleNum = 1;
        Row titleRow = sheet.getRow(startRow-titleNum);
        Map<String,String> usertitle = new HashMap<>();
        if(tableName.equals("UserPay")) {
            userPayMap userMap = new userPayMap();
            usertitle = userMap.userPay;
        }
        else {
            userInfoMap userMap = new userInfoMap();
            usertitle = userMap.userInfo;
        }
        setObjectProperty(titleRow,usertitle);

        List<ImportCell> lists = configuration.getImportCells();
        //根据实际情况，调整各自序列
        setImportCell(lists,usertitle);
        int phyRow = sheet.getLastRowNum();
        List<Map<String,Object>> results = Lists.newLinkedList();
        for (int t = startRow; t <= phyRow; t++) {
            Row row = sheet.getRow(t);
            if (row == null) {
                continue;
            }

            //poi获取正确行数很难。这里约定，前三个值都为空时，自动放弃该行
            if (isCellEmpty(row.getCell(0)) && isCellEmpty(row.getCell(1)) && isCellEmpty(row.getCell(2))) {
                continue;
            }
            Map<String, Object> maps = Maps.newLinkedHashMap();
//            maps.put(MapResult.IS_LINE_LEGAL_KEY, true);
            for (ImportCell importCell : lists) {
                setValue(maps, importCell, row, sb, t, startRow);
            }
            results.add(maps);
        }
        return results;
    }

    static  void  setObjectProperty(Row row,Map<String,String> init) throws FileImportException
    {
        Integer rowSize = row.getPhysicalNumberOfCells();
        String columName ="";
        for(int i =0;i<rowSize;i++){
            Cell cell = row.getCell(i);
            if(cell.getCellType() == Cell.CELL_TYPE_STRING && !StringUtils.isEmpty(cell.getStringCellValue())){
                String excelName = cell.getStringCellValue();
                for (Map.Entry<String, String> entry : init.entrySet()) {
                    columName = entry.getValue();//列名获取
                    if (excelName.equals(columName)) {
                        entry.setValue(Integer.toString(i));
                    }
                }
            }
        }

    }

    static void setImportCell(List<ImportCell> lists ,Map<String,String> usertitle){
        for (ImportCell list : lists) {
            String key = list.getKey();
            if(usertitle.containsKey(key)){
                if(isInteger(usertitle.get(key))) {//是数字
                    list.setNumber(Integer.valueOf(usertitle.get(key)));
                }else
                    list.setNumber(99);
            }
        }

    }

    public static boolean isInteger(String str) {
        Pattern pattern = Pattern.compile("^[-\\+]?[\\d]*$");
        return pattern.matcher(str).matches();
    }


    private boolean isCellEmpty(Cell cell) {
        if (cell == null) {
            return true;
        }
        if (cell.getCellType() == Cell.CELL_TYPE_BLANK) {
            return true;
        }
        if (cell.getCellType() == Cell.CELL_TYPE_STRING && StringUtils.isEmpty(cell.getStringCellValue())) {
            return true;
        }
        return false;
    }

    private void setErrMsg(String errMsg, Map<String, Object> maps, StringBuilder sb) {
        sb.append(errMsg);
        maps.put(MapResult.IS_LINE_LEGAL_KEY, false);
    }

    private void setValue(Map<String, Object> maps, ImportCell importCell, Row row, StringBuilder sb, int line, int startRow) throws FileImportException {
        int num = importCell.getNumber();
        int showLine = line + startRow;
        int showColumn = num + startRow;
//        maps.put(MapResult.LINE_NUM_KEY, showLine);//行列不赋值
        ImportCell.CellType cellType = importCell.getCellType();
        ImportCell.NullAble nullable = importCell.getNullAble();
        String errMsg = null;
        String key = importCell.getKey();
        Cell cell = row.getCell(num);
        int rawCellType = Cell.CELL_TYPE_BLANK;
        if (cell != null) {
            rawCellType = cell.getCellType();
        }
        if (rawCellType == Cell.CELL_TYPE_BLANK ||
                cell == null ||
                rawCellType == Cell.CELL_TYPE_STRING && StringUtils.isEmpty(cell.getStringCellValue())) {
            if (nullable == ImportCell.NullAble.NULL_ALLOWED) {
                maps.put(key, "");
            } else {
                errMsg = String.format("line:%d,column:%d is null,but null is not allowed by setting \n", showLine, showColumn);
                setErrMsg(errMsg, maps, sb);
            }
        } else {
            switch (cellType) {
                case INT:
                    if (rawCellType == Cell.CELL_TYPE_STRING) {
                        String temp = cell.getStringCellValue();
                        if (!StringUtils.isNumeric(temp)) {
                            errMsg = String.format("line:%d,column:%d is not number \n", showLine, showColumn);
                            setErrMsg(errMsg, maps, sb);
                        }
                        maps.put(key, Integer.valueOf(temp));
                    }
                    if (rawCellType == Cell.CELL_TYPE_NUMERIC) {
                        Double temp = cell.getNumericCellValue();
                        maps.put(key, temp.intValue());
                    }
                    break;

                case STRING:
                        cell.setCellType(Cell.CELL_TYPE_STRING);//强转
                        maps.put(key, cell.getStringCellValue());
                        break;
                case FLOAT:
                        Double temp = cell.getNumericCellValue();
                        maps.put(key, temp.floatValue());
                        break;
                case DATE:
                    if (rawCellType == Cell.CELL_TYPE_NUMERIC) {
                        Date date = DateUtil.getJavaDate(cell.getNumericCellValue());
                        maps.put(key, date);
                    } else {
                        errMsg = String.format("line:%d,column:%d is not date\n", showLine, showColumn);
                        setErrMsg(errMsg, maps, sb);
                    }
                    break;
                case BIGDECIMAL:
                    if (rawCellType == Cell.CELL_TYPE_NUMERIC) {
                        Double temp1 = cell.getNumericCellValue();
                        maps.put(key, BigDecimal.valueOf(temp1));
                    } else {
                        errMsg = String.format("line:%d,column:%d is not bigDecimal\n", showLine, showColumn);
                        setErrMsg(errMsg, maps, sb);
                    }
                    break;
                case DOUBLE:
                    if (rawCellType == Cell.CELL_TYPE_NUMERIC) {
                        Double temp1 = cell.getNumericCellValue();
                        maps.put(key, temp1);
                    } else {
                        errMsg = String.format("line:%d,column:%d is not double\n", showLine, showColumn);
                        setErrMsg(errMsg, maps, sb);
                    }
                    break;
            }


        }
    }

    public ExcelImportor(Configuration configuration) {
        this.configuration = configuration;
}

}
